//
//  DatabaseUtils.m
//  IWeighMyself
//
//  Created by Francisco Gindre on 8/24/10.
//  Copyright 2010 __MyCompanyName__. All rights reserved.
//

#import "DatabaseUtils.h"
#import <sqlite3.h>

@implementation DatabaseUtils

#define ID_COLUMN 0
#define DATE_COLUMN 1
#define KILOS_COLUMN 2
#define POUNDS_COLUMN 3
#define SENT_COLUMN 4


/*
 loads all queries from a table and specified database
 */

+(NSMutableArray*)queryAllEntriesFromDatabase:(NSString*)aDBPath fromTable:(NSString*)aTableName {
	
	// Setup the database object
	sqlite3 *database;
	
	// Init the animals Array
	NSMutableArray *entries = [[NSMutableArray alloc] init];
//	NSLog(@"%@",aDBPath);
	int openResult =sqlite3_open([aDBPath UTF8String], &database) ;
	// Open the database from the users filesystem
	if(openResult == SQLITE_OK) {
		// Setup the SQL Statement and compile it for faster access
		const char *sqlStatement = [[NSString stringWithFormat:@"select * from %s order by dateLogged",[aTableName UTF8String]] UTF8String];
		//const char *sqlStatement = [[NSString stringWithString:@"select * from weight"] UTF8String];
		sqlite3_stmt *compiledStatement;
		int prepareResult = sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL);
		
	//	NSLog(@" prepared result returns: %d", prepareResult);
	//	NSLog(@"could not prepare statemnt: %s\n", sqlite3_errmsg(database));
	
		if(prepareResult == SQLITE_OK) {
			// Loop through the results and add them to the feeds array
			while(sqlite3_step(compiledStatement) == SQLITE_ROW) {
				// Read the data from the result row and create new weight
				Weight *aWeight = [[Weight alloc] initWithID:sqlite3_column_int(compiledStatement,0) date:[NSDate dateWithTimeIntervalSince1970:sqlite3_column_double(compiledStatement,DATE_COLUMN)]
												 weightInKilos:[NSNumber numberWithDouble:sqlite3_column_double(compiledStatement,KILOS_COLUMN)]
												weightInPounds:[NSNumber numberWithDouble:sqlite3_column_double(compiledStatement,POUNDS_COLUMN)]
														isSent:sqlite3_column_int(compiledStatement,SENT_COLUMN)];
				
				// Add the weight object to the weights Array
				[entries addObject:aWeight];
				
				[aWeight release];
			}
		}
		// Release the compiled statement from memory
		sqlite3_finalize(compiledStatement);
		
	}
	sqlite3_close(database);
	
	//NSLog(@"all entries have %d elements", [entries count]);

	return entries;
}


/*
 loads all queries from a a table between the specified dates
 */

+(NSMutableArray*)queryAllEntriesFromDatabaseBetweenDates:(NSString*)aDBPath fromTable:(NSString*)aTableName from:(NSDate*)startDate to:(NSDate*)endDate {
	// Setup the database object
	sqlite3 *database;
	
	// Init the animals Array
	NSMutableArray *entries = [[NSMutableArray alloc] init];
	
	int openResult =sqlite3_open([aDBPath UTF8String], &database) ;
	// Open the database from the users filessytem
	if(openResult == SQLITE_OK) {
		// Setup the SQL Statement and compile it for faster access
		const char *sqlStatement = [[NSString stringWithFormat:@"select * from %s where dateLogged between %d and %d order by dateLogged",[aTableName UTF8String],
									[startDate timeIntervalSince1970],[endDate timeIntervalSince1970] ] UTF8String];
		sqlite3_stmt *compiledStatement;
		if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK) {
			// Loop through the results and add them to the feeds array
			while(sqlite3_step(compiledStatement) == SQLITE_ROW) {
				// Read the data from the result row and create new weight
				Weight *aWeight = [[Weight alloc] initWithID:sqlite3_column_int(compiledStatement,0) date:[NSDate dateWithTimeIntervalSince1970:sqlite3_column_double(compiledStatement,DATE_COLUMN)]
											   weightInKilos:[NSNumber numberWithDouble:sqlite3_column_double(compiledStatement,KILOS_COLUMN)]
											  weightInPounds:[NSNumber numberWithDouble:sqlite3_column_double(compiledStatement,POUNDS_COLUMN)]
													  isSent:sqlite3_column_int(compiledStatement,SENT_COLUMN)];
				
				// Add the weight object to the weights Array
				[entries addObject:aWeight];
				
				[aWeight release];
			}
		}
		// Release the compiled statement from memory
		sqlite3_finalize(compiledStatement);
		
	}
	sqlite3_close(database);
	
	
	NSLog(@"entries between dates have %d elements", [entries count]);
	return entries;
	
}
/*
 gets the newest weight from the database based on date selected by the user.
 */

+(Weight*)queryNewestWeight:(NSString*)aDBPath fromTable:(NSString*)aTableName {
	// Setup the database object
	sqlite3 *database;
	Weight *aWeight=nil;
		
	int openResult =sqlite3_open([aDBPath UTF8String], &database) ;
	// Open the database from the users filessytem
	if(openResult == SQLITE_OK) {
		// Setup the SQL Statement and compile it for faster access
		const char *sqlStatement = [[NSString stringWithFormat:@"select * from %s where dateLogged in (select max(dateLogged) from %s)",[aTableName UTF8String],[aTableName UTF8String] ] UTF8String];
		sqlite3_stmt *compiledStatement;
		int prepareResult = sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) ;
		if(prepareResult== SQLITE_OK) {
			// Loop through the results and add them to the feeds array
			while(sqlite3_step(compiledStatement) == SQLITE_ROW) {
				// Read the data from the result row and create new weight
				aWeight = [[Weight alloc] initWithID:sqlite3_column_int(compiledStatement,0) date:[NSDate dateWithTimeIntervalSince1970:sqlite3_column_double(compiledStatement,DATE_COLUMN)]
											   weightInKilos:[NSNumber numberWithDouble:sqlite3_column_double(compiledStatement,KILOS_COLUMN)]
											  weightInPounds:[NSNumber numberWithDouble:sqlite3_column_double(compiledStatement,POUNDS_COLUMN)]
													  isSent:sqlite3_column_int(compiledStatement,SENT_COLUMN)];
				
			
				
				
			}
		} else {
			if (prepareResult ==SQLITE_ERROR){
			UIAlertView * alert = [[UIAlertView alloc] initWithTitle:NSLocalizedString(@"SQLNewestWeightQueryEntryTitle",@"") message:NSLocalizedString(@"SQLNewestWeightQueryEntryMessage",@"") delegate:nil cancelButtonTitle:NSLocalizedString(@"AlertCloseButtonTitle",@"") otherButtonTitles:nil];
			[alert show];
			[alert release];
			}
		}

		// Release the compiled statement from memory
		sqlite3_finalize(compiledStatement);
		
	}
	sqlite3_close(database);
	
	
	
	return aWeight;
}

/*
 insert a weight into a weight database
 */

+(void) insertWeightInDatabase:(NSString*)aDBPath weightToInsert:(Weight*)aWeight intoTable:(NSString*)aTableName {
	// Setup the database object
	sqlite3 *database;
	
	//NSLog(@"weight: %@",[aWeight weightToString]);
	
	
	int openResult =sqlite3_open([aDBPath UTF8String], &database) ;
	// Open the database from the users filessytem
	if(openResult == SQLITE_OK) {
		// Setup the SQL Statement and compile it for faster access
		const char *sqlStatement = [[NSString stringWithFormat:@"insert into %s values (null,%ld,%f,%f,%d)",
									 [aTableName UTF8String],
									 (long)[[aWeight dateLogged]timeIntervalSince1970],
									 [[aWeight weightInKilos]doubleValue],[[aWeight weightInPounds]doubleValue],
									 ([aWeight wasSent])?1:0] UTF8String];
		sqlite3_stmt *compiledStatement;
		
		//prepare the resultset
		int preparedResult = sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) ;
		if(preparedResult == SQLITE_OK) {
			// Loop through the results and add them to the feeds array
			int step = sqlite3_step(compiledStatement);
			if( step != SQLITE_DONE) {
				UIAlertView *alert = [[UIAlertView alloc]initWithTitle:NSLocalizedString(@"SQLDeleteErrorTitle",@"")
															message:NSLocalizedString(@"SQLDeleteErrorMessage",@"")
															delegate:self 
															cancelButtonTitle:NSLocalizedString(@"CancelButtonTitle",@"")
													 otherButtonTitles:nil];
				[alert show];
				[alert release];
				
			}
		}
	
		//NSLog(@"database finalize: %d",sqlite3_finalize(compiledStatement));
		
	}
	//NSLog(@"database close %d",sqlite3_close(database));
}

+(void) deleteWeightFromDatabase:(NSString*)aDBPath weightToDelete:(Weight*)aWeight fromTable:(NSString*)aTableName {
	// Setup the database object
	sqlite3 *database;
	
	//NSLog(@"weight: %@",[aWeight weightToString]);
	
	
	int openResult =sqlite3_open([aDBPath UTF8String], &database) ;
	// Open the database from the users filessytem
	if(openResult == SQLITE_OK) {
		// Setup the SQL Statement and compile it for faster access
		const char *sqlStatement = [[NSString stringWithFormat:@"delete from %s where id=%d",
									 [aTableName UTF8String],
									 [aWeight weightID]] UTF8String];
		sqlite3_stmt *compiledStatement;
		
		//prepare the resultset
		int preparedResult = sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) ;
		if(preparedResult == SQLITE_OK) {
			// Loop through the results and add them to the feeds array
			int step = sqlite3_step(compiledStatement);
			if( step != SQLITE_DONE) {
				UIAlertView *alert = [[UIAlertView alloc]initWithTitle:NSLocalizedString(@"SQLInsertErrorTitle",@"")
															   message:NSLocalizedString(@"SQLInsertErrorMessage",@"")
															  delegate:self 
													 cancelButtonTitle:NSLocalizedString(@"CancelButtonTitle",@"")
													 otherButtonTitles:nil];
				[alert show];
				[alert release];
				
			}
		}
		
		//NSLog(@"database finalize: %d",sqlite3_finalize(compiledStatement));
		
	}
	//NSLog(@"database close %d",sqlite3_close(database));
}

/**
 
 updates an entry by ID
 
 */

+(void)updateEntryFromDatabase:(NSString*)aDBPath weight:(Weight*)aWeight fromTable:(NSString*)aTableName {
	// Setup the database object
	sqlite3 *database;
	
	//NSLog(@"weight: %@",[aWeight weightToString]);
	
	
	int openResult =sqlite3_open([aDBPath UTF8String], &database) ;
	// Open the database from the users filessytem
	if(openResult == SQLITE_OK) {
		// Setup the SQL Statement and compile it for faster access
		const char *sqlStatement = [[NSString stringWithFormat:@"update %s set dateLogged=%ld,kilos = %f,pounds = %f,wasSent = %d where id=%d",
									 
									 [aTableName UTF8String],
									 (long)[aWeight.dateLogged timeIntervalSince1970],
									 [aWeight.weightInKilos floatValue],
									 [aWeight.weightInPounds floatValue],
									 ([aWeight wasWeightSent])?1:0,
									 [aWeight weightID]] UTF8String];
		sqlite3_stmt *compiledStatement;
		
		//prepare the resultset
		int preparedResult = sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) ;
		if(preparedResult == SQLITE_OK) {
			// Loop through the results and add them to the feeds array
			int step = sqlite3_step(compiledStatement);
			if( step != SQLITE_DONE) {
				UIAlertView *alert = [[UIAlertView alloc]initWithTitle:NSLocalizedString(@"SQLInsertErrorTitle",@"")
															   message:NSLocalizedString(@"SQLInsertErrorMessage",@"")
															  delegate:self 
													 cancelButtonTitle:NSLocalizedString(@"CancelButtonTitle",@"")
													 otherButtonTitles:nil];
				[alert show];
				[alert release];
				
			}
		}
		
		//NSLog(@"database finalize: %d",sqlite3_finalize(compiledStatement));
		
	}
	//NSLog(@"database close %d",sqlite3_close(database));	
}

@end
